const express = require("express");
const router = express.Router();
const mysqlUtil = require('../utils/mysqlUtil');

// 分页查询
router.get('/page', async (req, res) => {
    const page = parseInt(req.query.page) || 1;
    const pageSize = parseInt(req.query.pageSize) || 10;
    const offset = (page - 1) * pageSize;
    const roleFilter = req.query.role ? `WHERE role = ?` : ''; // 根据是否有role参数决定是否添加WHERE子句
    const roleValue = req.query.role || null; // 如果没有role参数，则设置为null

    try {
        // 动态构建SQL语句
        let totalSql = `SELECT COUNT(*) AS total FROM users`;
        let listSql = `SELECT * FROM users`;

        if(roleFilter) {
            totalSql += ` ${roleFilter}`;
            listSql += ` ${roleFilter}`;
        }

        // 获取总记录数
        let totalResult;
        if(roleValue) {
            totalResult = await mysqlUtil.query(totalSql, [roleValue]);
        } else {
            totalResult = await mysqlUtil.query(totalSql);
        }
        const total = totalResult[0].total;

        // 分页查询用户列表
        let result;
        if(roleValue) {
            result = await mysqlUtil.query(`${listSql} LIMIT ? OFFSET ?`, [roleValue, pageSize, offset]);
        } else {
            result = await mysqlUtil.query(`${listSql} LIMIT ? OFFSET ?`, [pageSize, offset]);
        }

        res.send({
            status: true,
            message: '查询成功',
            data: result,
            page: page,
            pageSize: pageSize,
            total: total,
            code: '200'
        });
    } catch (error) {
        console.error(error); // 记录错误信息
        res.status(500).send({
            status: false,
            message: '查询失败',
            error: error.message,
            code: '500'
        });
    }
});

//id查询用户信息
router.get('/user/:id', (req, res) => {
    const id = req.params.id;
    mysqlUtil.query(`SELECT * FROM users WHERE id = ${id}`)
        .then(user => {
            if (user.length === 0) {
                return res.status(404).cc('用户不存在');
            }
            res.send({
                status: true,
                message: '查询成功',
                data: user[0]
            });
        })
        .catch(error => {
            res.status(500).cc(error);
        })
});
// 查询所有
router.get('/all', async (req, res) => {
    try {
        const result = await mysqlUtil.query('SELECT * FROM users');
        res.json({
            status: true,
            message: '查询成功',
            data: result,
            code: '200'
        });
    } catch (error) {
        console.error(error);
        res.status(500).json({
            status: false,
            message: '服务器内部错误',
            error: error.message
        });
    }
});

//新增用户
router.post('/add', async (req, res) => {
    const { username, password, role, age, height, avator, name, sign, taste, weight, targetweight, sex } = req.body;

    // 构建要插入的字段名和值数组
    const fields = [];
    const values = [];

    if (username !== undefined && username !== null) {
        fields.push('username');
        values.push(username);
    }
    if (password !== undefined && password !== null) {
        fields.push('password');
        values.push(password);
    }
    if (role !== undefined && role !== null) {
        fields.push('role');
        values.push(role);
    }
    if (age !== undefined && age !== null) {
        fields.push('age');
        values.push(age);
    }
    if (height !== undefined && height !== null) {
        fields.push('height');
        values.push(height);
    }
    if (avator !== undefined && avator !== null) {
        fields.push('avator');
        values.push(avator);
    }
    if (name !== undefined && name !== null) {
        fields.push('name');
        values.push(name);
    }
    if (sign !== undefined && sign !== null) {
        fields.push('sign');
        values.push(sign);
    }
    if (taste !== undefined && taste !== null) {
        fields.push('taste');
        values.push(taste);
    }
    if (weight !== undefined && weight !== null) {
        fields.push('weight');
        values.push(weight);
    }
    if (targetweight !== undefined && targetweight !== null) {
        fields.push('targetweight');
        values.push(targetweight);
    }
    if (sex !== undefined && sex !== null) {
        fields.push('sex');
        values.push(sex);
    }

    // 如果没有任何字段提供，则返回错误信息
    if (fields.length === 0) {
        return res.status(400).json({
            status: false,
            message: '至少需要提供一个字段'
        });
    }

    // 动态构建 SQL 插入语句
    const sql = `INSERT INTO users (${fields.join(', ')}) VALUES (${fields.map(() => '?').join(', ')})`;

    try {
        const result = await mysqlUtil.query(sql, values);
        res.json({
            status: true,
            message: '新增成功',
            data: {
                id: result.insertId
            },
            code: '200'
        });
    } catch (error) {
        console.error(error); // 记录错误日志以便调试
        res.status(500).json({
            status: false,
            message: '服务器内部错误',
            error: error.message
        });
    }
});

// 删除指定ID的用户
router.delete('/delete/:id', async (req, res) => {
    const userId = req.params.id;

    // 检查 userId 是否有效（例如，非空且为数字）
    if (!userId || isNaN(userId)) {
        return res.status(400).send({ 
            status: false,
            message: '无效的用户ID'
        });
    }

    try {
        // 使用参数化查询来防止 SQL 注入
        const result = await mysqlUtil.query('DELETE FROM users WHERE id = ?', [userId]);

        if (result.affectedRows === 0) {
            return res.status(404).send({
                status: false,
                message: '未找到要删除的数据'
            });
        }

        res.send({
            status: true,
            message: '删除成功',
            data: {
                affectedRows: result.affectedRows // 返回受影响的行数
            },
            code: '200'
        });
    } catch (error) {
        console.error(error); // 记录错误信息以便调试
        res.status(500).send({
            status: false,
            message: '服务器内部错误',
            error: error.message
        });
    }
});

//修改用户
router.put('/update', async (req, res) => {
    const { id, username, password, role, age, height, avator, name, sign, taste, weight, targetweight } = req.body;

    // 检查 id 是否存在
    if (!id) {
        return res.status(400).json({ 
            status: false,
            message: '用户 ID 不能为空'
        });
    }

    // 构建更新字段和值数组
    const updateFields = [];
    const values = [];

    // 动态添加需要更新的字段及其值
    if (username !== undefined && username !== null) {
        updateFields.push('username = ?');
        values.push(username);
    }
    if (password !== undefined && password !== null) {
        updateFields.push('password = ?');
        values.push(password);
    }
    if (role !== undefined && role !== null) {
        updateFields.push('role = ?');
        values.push(role);
    }
    if (age !== undefined && age !== null) {
        updateFields.push('age = ?');
        values.push(age);
    }
    if (height !== undefined && height !== null) {
        updateFields.push('height = ?');
        values.push(height);
    }
    if (avator !== undefined && avator !== null) {
        updateFields.push('avator = ?');
        values.push(avator);
    }
    if (name !== undefined && name !== null) {
        updateFields.push('name = ?');
        values.push(name);
    }
    if (sign !== undefined && sign !== null) {
        updateFields.push('sign = ?');
        values.push(sign);
    }
    if (taste !== undefined && taste !== null) {
        updateFields.push('taste = ?');
        values.push(taste);
    }
    if (weight !== undefined && weight !== null) {
        updateFields.push('weight = ?');
        values.push(weight);
    }
    if (targetweight !== undefined && targetweight !== null) {
        updateFields.push('targetweight = ?');
        values.push(targetweight);
    }

    // 如果没有要更新的字段，直接返回成功
    if (updateFields.length === 0) {
        return res.json({
            status: true,
            message: '无需更新任何字段'
        });
    }

    // 构建 SQL 查询
    const sql = `UPDATE users SET ${updateFields.join(', ')} WHERE id = ?`;
    values.push(id);

    try {
        const result = await mysqlUtil.query(sql, values);
        if (result.affectedRows === 0) {
            return res.status(404).json({
                status: false,
                message: '未找到要更新的数据'
            });
        }
        res.json({
            status: true,
            message: '修改成功',
            data: req.body,
            code: '200'
        });
    } catch (error) {
        console.error(error); // 记录错误日志以便调试
        res.status(500).json({
            status: false,
            message: '服务器内部错误',
            error: error.message
        });
    }
});

router.post("/login", async (req, res) => {

    const { username, password } = req.body;

    // 验证用户名和密码
    const user = await mysqlUtil.query(`SELECT * FROM users WHERE username = '${username}' AND password = '${password}'`);
    console.log(user);
    //返回结果小于1就是用户名或密码错误
    if (user.length != 1) {
        res.send({
            status: true,
            message: '用户名或者密码不正确',
            code: '500'
        });
    } else {
        res.send({
            status: true,
            msg: "登录成功",
            code: '200',
            data: user[0]
        });
    }
    
});

module.exports = router;